/*
 * To change this template, choose Tools | Templates and open the template in
 * the editor.
 */
package eypweb_bsc;

import java.net.URL;
import java.sql.*;
import java.util.ArrayList;
import java.util.ResourceBundle;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.scene.control.*;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.scene.input.MouseEvent;

/**
 *
 * @author Kohalmi Istvan
 */

// vagyonkezeloket rogzito kepernyot megvalosito osztaly
public class TrusteeRegistry implements Initializable {

    @FXML
    private Button btnNew;
    @FXML
    private Button btnMod;
    @FXML
    private Button btnCan;
    @FXML
    private Button btnRes;
    @FXML
    private Button btnCom;
    @FXML
    private Button btnEsc;
    @FXML
    private Label errorLabel;
    @FXML
    private TextField tfName;
    @FXML
    private TextField tfShort;
    @FXML
    private TextField tfZip;
    @FXML
    private TextField tfCity;
    @FXML
    private TextField tfAddress;
    @FXML
    private TextField tfTel;
    @FXML
    private TextField tfMobile;
    @FXML
    private TextField tfFax;
    @FXML
    private TextField tfMail;
    @FXML
    private TextField tfConnection;
    @FXML
    private TextField tfAssignment;
    @FXML
    private TextField tfStart;
    @FXML
    private TextField tfEnd;
    ArrayList<TextField> textFields = new ArrayList<TextField>();
    @FXML
    private TableView<Trustee> masterData;

    public TrusteeRegistry() {
        super();
        textFields.add(tfName);
        textFields.add(tfShort);
        textFields.add(tfZip);
        textFields.add(tfCity);
        textFields.add(tfAddress);
        textFields.add(tfTel);
        textFields.add(tfMobile);
        textFields.add(tfFax);
        textFields.add(tfMail);
        textFields.add(tfConnection);
        textFields.add(tfAssignment);
    }

    @FXML
    private void handleNewButtonAction(ActionEvent event) {
        if (btnNew.getText().equals("Új elem")) {
            btnMod.setDisable(true);
            btnCom.setDisable(true);
            btnRes.setDisable(true);
            btnCan.setDisable(false);
            btnNew.setText("Rendben");
            for (TextField tf : textFields) {
                tf.clear();
            }
        } else {
            if (tfName.getText().isEmpty() || tfShort.getText().isEmpty()
                    || tfZip.getText().isEmpty() || tfCity.getText().isEmpty()
                    || tfAddress.getText().isEmpty() || tfTel.getText().isEmpty()
                    || tfConnection.getText().isEmpty() || tfMail.getText().isEmpty()) {
                errorLabel.setText("Egy kötelező mező nincs kitöltve!");
                return;
            }
            try {
                Date start = Date.valueOf(tfStart.getText());
                Date end = Date.valueOf(tfEnd.getText());
                if (start.after(end)) {
                    errorLabel.setText("A kezdődátum nem lehet későbbi, mint a végdátum!");
                    return;
                }
            } catch (Exception e) {
                errorLabel.setText("A dátum nincs megfelelően formázva!");
                return;
            }
            Trustee trustee = new Trustee();
            trustee.setId(0);
            trustee.setIdTrustee(0);
            trustee.setName(tfName.getText());
            trustee.setShortName(tfShort.getText());
            trustee.setZip(tfZip.getText());
            trustee.setCity(tfCity.getText());
            trustee.setAddress(tfAddress.getText());
            trustee.setTel(tfTel.getText());
            trustee.setMobile(tfMobile.getText());
            trustee.setFax(tfFax.getText());
            trustee.setMail(tfMail.getText());
            trustee.setConnection(tfConnection.getText());
            trustee.setAssignment(tfAssignment.getText());
            trustee.setStartDate(tfStart.getText());
            trustee.setEndDate(tfEnd.getText());
            trustee.setModified();
            masterData.getItems().add(trustee);
            btnMod.setDisable(false);
            btnRes.setDisable(false);
            btnCom.setDisable(false);
            btnNew.setText("Új elem");
        }
    }

    @FXML
    private void handleModifyButtonAction(ActionEvent event) {
        if (btnMod.getText().equals("Módosít")) {
            Trustee selectedItem = masterData.getSelectionModel().getSelectedItem();
            if (selectedItem != null) {
                tfName.setText(selectedItem.getName());
                tfShort.setText(selectedItem.getShortName());
                tfZip.setText(selectedItem.getZip());
                tfCity.setText(selectedItem.getCity());
                tfAddress.setText(selectedItem.getAddress());
                tfTel.setText(selectedItem.getTel());
                tfMobile.setText(selectedItem.getMobile());
                tfFax.setText(selectedItem.getFax());
                tfMail.setText(selectedItem.getMail());
                tfConnection.setText(selectedItem.getConnection());
                tfAssignment.setText(selectedItem.getAssignment());
                tfStart.setText(selectedItem.getStartDate());
                tfEnd.setText(selectedItem.getEndDate());
                btnMod.setText("Rendben");
                btnNew.setDisable(true);
                btnCom.setDisable(true);
                btnRes.setDisable(true);
                btnCan.setDisable(false);
            }
        } else {
            if (tfName.getText().isEmpty() || tfShort.getText().isEmpty()
                    || tfZip.getText().isEmpty() || tfCity.getText().isEmpty()
                    || tfAddress.getText().isEmpty() || tfTel.getText().isEmpty()
                    || tfConnection.getText().isEmpty() || tfMail.getText().isEmpty()) {
                errorLabel.setText("Egy kötelező mező nincs kitöltve!");
                return;
            }
            try {
                Date start = Date.valueOf(tfStart.getText());
                Date end = Date.valueOf(tfEnd.getText());
                if (start.after(end)) {
                    errorLabel.setText("A kezdődátum nem lehet későbbi, mint a végdátum!");
                    return;
                }
            } catch (Exception e) {
                errorLabel.setText("A dátum nincs megfelelően formázva!");
                return;
            }
            Trustee selectedItem = masterData.getSelectionModel().getSelectedItem();
            if (selectedItem != null) {
                selectedItem.setName(tfName.getText());
                selectedItem.setShortName(tfShort.getText());
                selectedItem.setZip(tfZip.getText());
                selectedItem.setCity(tfCity.getText());
                selectedItem.setAddress(tfAddress.getText());
                selectedItem.setTel(tfTel.getText());
                selectedItem.setMobile(tfMobile.getText());
                selectedItem.setFax(tfFax.getText());
                selectedItem.setMail(tfMail.getText());
                selectedItem.setConnection(tfConnection.getText());
                selectedItem.setAssignment(tfAssignment.getText());
                selectedItem.setStartDate(tfStart.getText());
                selectedItem.setEndDate(tfEnd.getText());
                selectedItem.setModified();
                btnMod.setText("Módosít");
                btnNew.setDisable(false);
                btnCom.setDisable(false);
                btnRes.setDisable(false);
            }
        }
    }

    @FXML
    private void handleCancelButtonAction(ActionEvent event) {
        btnNew.setDisable(false);
        btnMod.setDisable(false);
        btnRes.setDisable(false);
        btnCom.setDisable(false);
        btnNew.setText("Új elem");
        btnMod.setText("Módosít");
        btnCan.setDisable(true);
    }

    @FXML
    private void handleResetButtonAction(ActionEvent event) {
        Director director = Director.getInstance();
        AccessInfo ai = director.getAccessInfo();
        ObservableList<Trustee> trustees = FXCollections.observableArrayList();
        try {
            Connection con = DriverManager.getConnection("jdbc:sqlserver://" + ai.getIPAddress() + ":" + ai.getPort() + ";databaseName=" + ai.getDBName(), ai.getUser(), ai.getPass());
            String TrusteeQuery = "SELECT * FROM vagyonkezelo";
            PreparedStatement stmt = con.prepareStatement(TrusteeQuery);
            ResultSet results = stmt.executeQuery();
            while (results.next()) {
                Trustee trustee = new Trustee();
                trustee.setId(results.getInt(1));
                trustee.setIdTrustee(results.getInt(2));
                trustee.setName(results.getNString(3));
                trustee.setShortName(results.getNString(4));
                trustee.setZip(results.getNString(5));
                trustee.setCity(results.getNString(6));
                trustee.setAddress(results.getNString(7));
                trustee.setTel(results.getNString(8));
                trustee.setMobile(results.getNString(9));
                trustee.setFax(results.getNString(10));
                trustee.setMail(results.getNString(11));
                trustee.setConnection(results.getNString(12));
                trustee.setAssignment(results.getNString(13));
                trustee.setStartDate(results.getDate(14).toString());
                trustee.setEndDate(results.getDate(15).toString());
                trustees.add(trustee);
            }
            con.close();
        } catch (SQLException sqle) {
            errorLabel.setText(sqle.getMessage());
        }
        masterData.getItems().clear();
        masterData.getItems().addAll(trustees);
    }

    @FXML
    private void handleCommitButtonAction(ActionEvent event) {
        Director director = Director.getInstance();
        AccessInfo ai = director.getAccessInfo();
        ObservableList<Trustee> trustees = masterData.getItems();
        try {
            Connection con = DriverManager.getConnection("jdbc:sqlserver://" + ai.getIPAddress() + ":" + ai.getPort() + ";databaseName=" + ai.getDBName(), ai.getUser(), ai.getPass());
            String insertAction = "exec AddOrModifyTrustee ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?";
            for (Trustee trst : trustees) {
                if (trst.getModified()){
                    PreparedStatement insertStmt = con.prepareStatement(insertAction);
                    insertStmt.setInt(1, trst.getId());
                    insertStmt.setInt(2, trst.getIdTrustee());
                    insertStmt.setNString(3, trst.getName());
                    insertStmt.setNString(4, trst.getShortName());
                    insertStmt.setNString(5, trst.getZip());
                    insertStmt.setNString(6, trst.getCity());
                    insertStmt.setNString(7, trst.getAddress());
                    insertStmt.setNString(8, trst.getTel());
                    insertStmt.setNString(9, trst.getMobile());
                    insertStmt.setNString(10, trst.getFax());
                    insertStmt.setNString(11, trst.getMail());
                    insertStmt.setNString(12, trst.getConnection());
                    insertStmt.setNString(13, trst.getAssignment());
                    insertStmt.setDate(14, Date.valueOf(trst.getStartDate()));
                    insertStmt.setDate(15, Date.valueOf(trst.getEndDate()));
                    insertStmt.executeUpdate();
                }
            }
            con.close();
        } catch (SQLException sqle) {
            errorLabel.setText(sqle.getMessage());
        }
    }

    @FXML
    private void handleEscapeButtonAction(ActionEvent event) {
        Director director = Director.getInstance();
        director.initializeMainMenu();
    }
    
    @FXML
    private void handleCSVButtonAction(ActionEvent event) {
        CSVHandler.exportTrustee(masterData.getItems());
    }

    @FXML
    private void handleXLSButtonAction(ActionEvent event) {
        XLSHandler.exportTrustee(masterData.getItems());
    }

    @FXML
    private void handleXMLButtonAction(ActionEvent event) {
        XMLHandler.exportTrustee(masterData.getItems());
    }

    @FXML
    private void handleRowSelect(MouseEvent event) {
        if (masterData.getSelectionModel().getSelectedItem() != null) {
            btnMod.setDisable(false);
        } else {
            btnMod.setDisable(true);
        }
    }

    public void initialize(URL url, ResourceBundle bundle) {
        handleResetButtonAction(new ActionEvent());

        TableColumn<Trustee, String> nameCol = new TableColumn<Trustee, String>("Teljes név");
        nameCol.setCellValueFactory(new PropertyValueFactory("name"));

        TableColumn<Trustee, String> shortCol = new TableColumn<Trustee, String>("Rövid név");
        shortCol.setCellValueFactory(new PropertyValueFactory("shortName"));

        TableColumn<Trustee, String> zipCol = new TableColumn<Trustee, String>("Irányítószám");
        zipCol.setCellValueFactory(new PropertyValueFactory("zip"));
        
        TableColumn<Trustee, String> cityCol = new TableColumn<Trustee, String>("Település");
        cityCol.setCellValueFactory(new PropertyValueFactory("city"));
        
        TableColumn<Trustee, String> addressCol = new TableColumn<Trustee, String>("Cím");
        addressCol.setCellValueFactory(new PropertyValueFactory("address"));
        
        TableColumn<Trustee, String> telCol = new TableColumn<Trustee, String>("Telefon");
        telCol.setCellValueFactory(new PropertyValueFactory("tel"));
        
        TableColumn<Trustee, String> mobileCol = new TableColumn<Trustee, String>("Mobil");
        mobileCol.setCellValueFactory(new PropertyValueFactory("mobile"));
        
        TableColumn<Trustee, String> faxCol = new TableColumn<Trustee, String>("Fax");
        faxCol.setCellValueFactory(new PropertyValueFactory("fax"));
        
        TableColumn<Trustee, String> mailCol = new TableColumn<Trustee, String>("E-mail");
        mailCol.setCellValueFactory(new PropertyValueFactory("mail"));
        
        TableColumn<Trustee, String> connectionCol = new TableColumn<Trustee, String>("Kapcsolat");
        connectionCol.setCellValueFactory(new PropertyValueFactory("connection"));
        
        TableColumn<Trustee, String> assignmentCol = new TableColumn<Trustee, String>("Beosztás");
        assignmentCol.setCellValueFactory(new PropertyValueFactory("assignment"));
        
        TableColumn<Trustee, String> startCol = new TableColumn<Trustee, String>("Hatály kezdete");
        startCol.setCellValueFactory(new PropertyValueFactory("startDate"));

        TableColumn<Trustee, String> endCol = new TableColumn<Trustee, String>("Hatály vége");
        endCol.setCellValueFactory(new PropertyValueFactory("endDate"));
        
        masterData.getColumns().setAll(nameCol, shortCol, zipCol, cityCol, addressCol, telCol, 
                mobileCol, faxCol, mailCol, connectionCol, assignmentCol, startCol, endCol);
    }
}